package excel.controller;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.io.IoUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Color;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.*;
import cn.hutool.poi.excel.ExcelWriter;
/*import excel.Listener.ExcelReadExample;*/
import excel.entity.ExcelDTO;
import excel.entity.ResultObject;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/excel")
public class ExcelController {
    /**
     * 工作簿
     */
    private Workbook wb;
    /**
     * 导入的excel类型
     */
    private String excelType;
    /**
     * 2003-2007版本
     */
    private static final String EXCEL_TYPE_2003 = "xls";
    /**
     * 2007以后版本
     */
    private static final String EXCEL_TYPE_2007 = "xlsx";

    /**
     * 导入
     */
    @PostMapping("/import")
    public ResultObject excelFile(@RequestParam("file") MultipartFile file) throws Exception {
        ResultObject resultObject = new ResultObject();
        if (ObjectUtil.isNull(file)) {
            resultObject.setMsg("文件空");
            return resultObject;
        }
        //获取文件名称
        String originalFilename = file.getOriginalFilename();
        if (!(originalFilename.contains("xls") || originalFilename.contains("xlsx"))) {
            resultObject.setMsg("只支持xls或xlsx格式文件导入");
            return resultObject;
        }
        if(originalFilename.contains("xlsx")){
            this.excelType = "xlsx";
        }else {
            this.excelType = "xls";
        }

        List<ExcelDTO> list = new ArrayList<>();
        InputStream inputStream = null ;
        ExcelReader reader = null ;
        try {
            inputStream = file.getInputStream();
            reader = ExcelUtil.getReader(inputStream);
            if (reader == null) {
                resultObject.setMsg("文件空");
                return resultObject;
            }

            Workbook workbook = reader.getWorkbook();
            this.wb =workbook;
            //根据工作表数量
            for (int i = 0; i<1;i++){
                Sheet sheet = workbook.getSheetAt(i); // 获取第一个工作表

                for (int k = 0 ; k <=  sheet.getLastRowNum() ; k++) {
                    Row row = sheet.getRow(k);
                    if(row== null){
                        continue;
                    }
                    int flag = 0;
                    ExcelDTO excelDTO = new ExcelDTO();
                    for (int j = 0; j<row.getLastCellNum();j++){
                        Cell cell = row.getCell(j);
                        if(cell == null ){
                            flag++;
                            continue;
                        }
                        String cellToHtml = getCellToHtml(cell,sheet,flag);
//                        System.out.println("cellToHtml = " + cellToHtml);
//                        excelDTO.setContent(cellToHtml);
                        //设置属性值
                        if(StringUtils.isNotBlank(cellToHtml)){
                            if (flag == 0){
                                excelDTO.setTitle(cellToHtml);
                                flag++;
                            }else if (flag == 1){
                                excelDTO.setIntroduction(cellToHtml);
                                flag++;
                            }else if (flag == 2){
                                cellToHtml = "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"+cellToHtml+"<br>";
                                if(StringUtils.isNotBlank(excelDTO.getTitle())){
                                    excelDTO.setContent(cellToHtml);
                                }else {
                                    ExcelDTO excelDTO1 = list.get(list.size() - 1);
                                    excelDTO1.setContent(excelDTO1.getContent()+cellToHtml);
                                }
                                flag++;
                            }else{
                                flag=0;
                            }
                        }else {
                            flag++;
                        }
                    }
                    if(StringUtils.isNotBlank(excelDTO.getTitle())){
                        list.add(excelDTO);
                    }
                }
                if(CollectionUtil.isNotEmpty(list)){
                    // dto.remove(0);
                    resultObject.setData(list);
                }
            }
        }finally {
            IoUtil.close(inputStream);
            IoUtil.close(reader);
        }


        return resultObject ;
    }
    /**
     * 实现方法
     * 将单元格内容转为html格式
     */
    public String getCellToHtml(Cell cell,Sheet sheet,int flag){
//        if(StringUtils.isBlank(cell.getStringCellValue())){
//            return null;
//        }
        if(flag == 2){
            String result = new String();
            switch (cell.getCellType()) {
                case NUMERIC:// 数字类型
                    if (DateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                        SimpleDateFormat sdf = null;
                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {// 日期
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        Date date = cell.getDateCellValue();
                        result = sdf.format(date);
                    } else if (cell.getCellStyle().getDataFormat() == 58) {
                        // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        result = sdf.format(date);
                    } else {
                        double value = cell.getNumericCellValue();
                        CellStyle style = cell.getCellStyle();
                        DecimalFormat format = new DecimalFormat();
                        String temp = style.getDataFormatString();
                        // 单元格设置成常规
                        if (temp.equals("General")) {
                            format.applyPattern("#");
                        }
                        result = format.format(value);
                    }
                    break;
                case STRING:// String类型
                    // 遍历每个文本片段
                    StringBuilder htmlCode = new StringBuilder();
                    if(cell.getRichStringCellValue() instanceof XSSFRichTextString){
                        XSSFRichTextString xssfRichText = (XSSFRichTextString) cell.getRichStringCellValue();
                        for (int i = 0; i < xssfRichText.numFormattingRuns(); i++) {
                            int start = xssfRichText.getIndexOfFormattingRun(i);
                            int end = xssfRichText.length();
                            String textHead = "";
                            if (i + 1 < xssfRichText.numFormattingRuns()) {
                                end = xssfRichText.getIndexOfFormattingRun(i + 1);
                            }
                            String  textContent = xssfRichText.getString().substring(start, end);
                            //XSSFRichTextString.numFormattingRuns()获取不到富文本头文本样式，这时候文本样式取单元格样式
                            if(start == 0){
                                XSSFFont font = (XSSFFont)wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                                textContent = unionHtmlStyleFormFontV2(font, textContent, textHead,wb,cell,sheet);
                            }else{
                                XSSFFont font = xssfRichText.getFontAtIndex(start);
                                textContent = unionHtmlStyleFormFontV2(font, textContent, textHead,wb,cell,sheet);
                            }
                            htmlCode.append(textContent);
                            //cell.setCellValue(htmlCode.toString());
                        }
                        //2003-2007版本的excel文件
                        //单元格文本只存在一种样式
                        if(cell.getRichStringCellValue().numFormattingRuns() == 0){
                            XSSFFont font = (XSSFFont) wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                            String  textContent = cell.getRichStringCellValue().getString();
                            textContent = unionHtmlStyleFormFontV2(font, textContent, null,wb,cell,sheet);
                            htmlCode.append(textContent);
                        }

                    }else if (cell.getRichStringCellValue() instanceof HSSFRichTextString){
                        HSSFRichTextString hssfRichText = (HSSFRichTextString) cell.getRichStringCellValue();
                        //多文本多样式
                        int nextEnd = 0; //上一次结束索引
                        for (int i = 0; i < hssfRichText.numFormattingRuns(); i++) {
                            int start = hssfRichText.getIndexOfFormattingRun(i);
                            int end = hssfRichText.length();
                            String textHead = "";
                            if (i + 1 < hssfRichText.numFormattingRuns()) {
                                end = hssfRichText.getIndexOfFormattingRun(i + 1);
                            }
                            //HSSFRichTextString.numFormattingRuns()获取不到单元格头文本样式，这时候文本样式取单元格样式
                            if(start > nextEnd){
                                textHead = hssfRichText.getString().substring(nextEnd, start);
                                HSSFFont fontHead = (HSSFFont)wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                                textHead = unionHtmlStyleFormFont(fontHead, textHead, null,wb,cell,sheet);
                            }
                            nextEnd = end;
                            // 获取文本片段
                            String  textContent = hssfRichText.getString().substring(start, end);
                            // 获取文本片段样式
                            HSSFFont font = (HSSFFont)wb.getFontAt(hssfRichText.getFontAtIndex(start));
                            textContent = unionHtmlStyleFormFont(font, textContent, textHead,wb,cell,sheet);
                            htmlCode.append(textContent);
                        }
                        //2003-2007版本的excel文件
                        //单元格文本只存在一种样式
                        if(cell.getRichStringCellValue().numFormattingRuns() == 0){
                            HSSFFont font = (HSSFFont) wb.getFontAt(cell.getCellStyle().getFontIndexAsInt());
                            String  textContent = cell.getRichStringCellValue().getString();
                            textContent = unionHtmlStyleFormFont(font, textContent, null,wb,cell,sheet);
                            htmlCode.append(textContent);
                        }
                    }
                    cell.setCellValue(htmlCode.toString());
                    result = cell.getRichStringCellValue().toString();
                    //result = result.replace("\n","<br>");
                    break;
                case BLANK:
                    result = "";
                    break;
                default:
                    result = "";
                    break;
            }
            if(StringUtils.isNotBlank(result)) {
                return result.toString();
                //return  "<p>" + result.toString().replaceAll("\n", "</p><p>") + "</p>";
            }
        }else {
            cell.setCellType(CellType.STRING);
            return cell.getStringCellValue();
        }
        return null;
    }


    /**
     * 拼接html样式（根据具体业务需求实现）
     */
    private static String unionHtmlStyleFormFont(HSSFFont font, String textContent, String textHead,Workbook wb,Cell cell,Sheet sheet){
        if(ObjectUtil.isNotNull(font)){
            //加粗
            if(font.getBold()){
                textContent = "<strong>" + textContent + "</strong>";
            }

            //斜体
            if(font.getItalic()){
                textContent = "<em>" + textContent + "</em>";
            }
            //上下标
            switch (font.getTypeOffset()){
                case Font.SS_SUPER:
                    textContent = "<sup>" + textContent + "</sup>";
                    break;
                case Font.SS_SUB:
                    textContent = "<sub>" + textContent + "</sub>";
                default:
                    break;
            }
            //水平删除斜线
            if(font.getStrikeout()){
                textContent = "<span style=\"text-decoration: line-through;\">" + textContent + "</span>";
            }
            //下划线
            if(font.getUnderline() == Font.U_SINGLE){
                textContent = "<u>" + textContent + "</u>";
            }


            //设置颜色

            short fontColor = font.getColor();
            HSSFPalette palette = ((HSSFWorkbook) wb).getCustomPalette(); // 类HSSFPalette用于求的颜色的国际标准形式
            HSSFColor hc = palette.getColor(fontColor);
            String fontColorStr = convertToStardColor(hc);
            if (fontColorStr != null && !"".equals(fontColorStr.trim())) {
                textContent = "<span style='color:"+fontColorStr+"' >"+textContent+"</span>";
            }
            //设置字体 font-size: 20px;font-family: 微软雅黑;
            textContent = "<div style= 'font-family: "+font.getFontName()+"; font-size:"+font.getFontHeightInPoints()+"px;display:inline-block;'>"+textContent+"</div>";
            System.out.println("Font Name: " + font.getFontName());
            System.out.println("Font Size: " + font.getFontHeightInPoints());
        }
        if(StringUtils.isNotBlank(textHead)){
            textContent = textHead + textContent;
        }
        return textContent;
    }

    /**
     * 拼接html样式（根据具体业务需求实现）
     */
    private static String unionHtmlStyleFormFontV2(XSSFFont font, String textContent, String textHead,Workbook wb,Cell cell,Sheet sheet){
        if(ObjectUtil.isNotNull(font)){
            //加粗
            if(font.getBold()){
                textContent = "<strong>" + textContent + "</strong>";
            }

            //斜体
            if(font.getItalic()){
                textContent = "<em>" + textContent + "</em>";
            }
            //上下标
            switch (font.getTypeOffset()){
                case Font.SS_SUPER:
                    textContent = "<sup>" + textContent + "</sup>";
                    break;
                case Font.SS_SUB:
                    textContent = "<sub>" + textContent + "</sub>";
                default:
                    break;
            }
            //水平删除斜线
            if(font.getStrikeout()){
                textContent = "<span style=\"text-decoration: line-through;\">" + textContent + "</span>";
            }
            //下划线
            if(font.getUnderline() == Font.U_SINGLE){
                textContent = "<u>" + textContent + "</u>";
            }
            //设置颜色
            XSSFColor xc = font.getXSSFColor();
            if (xc != null && !"".equals(xc)) {
                textContent  ="<span style='color:#" + xc.getARGBHex().substring(2) + ";"+"' >"+textContent+"</span>"; // 字体颜色
            }
            CellStyle cellStyle = cell.getCellStyle(); // 类HSSFPalette用于求的颜色的国际标准形式
            XSSFColor bgColor = (XSSFColor) cellStyle.getFillForegroundColorColor();
            if (bgColor != null && !"".equals(bgColor)) {
                textContent ="<span style='background-color:#" + bgColor.getARGBHex().substring(2) + ";"+"' >"+textContent+"</span>"; // 背景颜色
            }
        }
        if(StringUtils.isNotBlank(textHead)){
            textContent = textHead + textContent;
        }
        return textContent;
    }

    private static String convertToStardColor(HSSFColor hc) {
        StringBuffer sb = new StringBuffer("");
        if (hc != null) {
            if (HSSFColor.HSSFColorPredefined.AUTOMATIC.getIndex() == hc.getIndex()) {
                return null;
            }
            sb.append("#");
            for (int i = 0; i < hc.getTriplet().length; i++) {
                sb.append(fillWithZero(Integer.toHexString(hc.getTriplet()[i])));
            }
        }
        return sb.toString();
    }

    private static String fillWithZero(String str) {
        if (str != null && str.length() < 2) {
            return "0" + str;
        }
        return str;
    }


}